import pymysql
from pymysql import cursors


def get_connection():
    # 连接数据库
    conn = pymysql.connect(host='localhost',
                           user='root',
                           password='admin',
                           db='cov',
                           cursorclass=cursors.DictCursor,
                           charset='utf8')
    cursor = conn.cursor()
    return conn, cursor


def close_connection(conn, cursor):
    # 关闭数据库
    cursor.close()
    conn.close()


def findAll():
    # 查询所有数据
    conn, cursor = get_connection()
    sql = "SELECT * FROM risk_area"
    cursor.execute(sql)
    result = cursor.fetchall()
    close_connection(conn, cursor)
    return result


def findTotal():
    # 查询总数
    conn, cursor = get_connection()
    sql = "SELECT count(*) FROM risk_area"
    cursor.execute(sql)
    result = cursor.fetchone()
    close_connection(conn, cursor)
    return result['count(*)']


def findPage(data):
    # 分页查询数据
    conn, cursor = get_connection()
    sql = "SELECT * FROM risk_area LIMIT %s, %s"
    cursor.execute(sql, (data['currentPage'] * 10 - 10, data['pageSize']))
    result = cursor.fetchall()
    close_connection(conn, cursor)
    return result


def clickfindTotal(queryString):
    # 查询总数
    conn, cursor = get_connection()
    sql = "SELECT count(*) FROM risk_area WHERE province like %s"
    cursor.execute(sql, '%' + queryString + '%')
    result = cursor.fetchone()
    close_connection(conn, cursor)
    return result['count(*)']


def clickfindPage(data):
    # 分页查询数据
    conn, cursor = get_connection()
    sql = "SELECT * FROM risk_area WHERE province like %s LIMIT %s, %s"
    cursor.execute(sql, ('%' + data['queryString'] + '%', data['currentPage'] * 10 - 10, data['pageSize']))
    result = cursor.fetchall()
    close_connection(conn, cursor)
    return result


def findByProvince(province):
    # 根据省份查询数据
    conn, cursor = get_connection()
    sql = "SELECT * FROM risk_area WHERE province like %s"
    cursor.execute(sql, '%' + province + '%')
    result = cursor.fetchall()
    close_connection(conn, cursor)
    return result


def delete(id):
    # 根据id删除数据
    conn, cursor = get_connection()
    sql = "DELETE FROM risk_area WHERE id = %s"
    cursor.execute(sql, id)
    conn.commit()
    close_connection(conn, cursor)


def add(data):
    # 添加数据
    conn, cursor = get_connection()
    sql = "INSERT INTO risk_area (end_update_time, province,city,county,address,type) VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, [data['end_update_time'], data['province'], data['city'], data['county'], data['address'],
                         data['type']])
    conn.commit()
    close_connection(conn, cursor)


def findById(id):
    # 根据id查询数据
    conn, cursor = get_connection()
    sql = "SELECT * FROM risk_area WHERE id = %s"
    cursor.execute(sql, id)
    result = cursor.fetchone()
    close_connection(conn, cursor)
    return result


def edit(data):
    # 编辑数据
    conn, cursor = get_connection()
    sql = "UPDATE risk_area SET end_update_time=%s, province=%s, city=%s, county=%s, address=%s, type=%s WHERE id = %s"
    cursor.execute(sql, [data['end_update_time'], data['province'], data['city'], data['county'], data['address'],
                         data['type'], data['id']])
    conn.commit()
    close_connection(conn, cursor)
